package com.chpro.excel;


import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.chpro.lang.Lang;
/**
 * excel工具类
 * @author Administrator
 *
 */
public class ExcelUtil {
	private static Log log =  LogFactory.getLog(ExcelUtil.class);
	
	/**
	 * 将一个List集合 转换 为excel 文件流
	 * @param list 集合
	 * @return 流
	 */
	public static OutputStream getExcelByList(List<Map<String,Object>> list,OutputStream out){
		
		Workbook workbook = new HSSFWorkbook();
        
        String[] headers = Lang.getStringArray(list.get(0).keySet().toArray());
//        if(list.size()>0){
//        	list.remove(0);
//        }
        List<List<String>> data = Lang.getListUtil(list,headers);
        
        
        ExcelUtil excelUtil = new ExcelUtil();
        try {
			excelUtil.exportExcel(workbook,0,"sheet1", headers,  data, out);
			workbook.write(out);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
        
        return out;
	}
	
	/**
	 * 将一个 excel文件流 转换成 List集合
	 * @return List集合
	 */
	public static List<Map<String,Object>> getListByExcel(InputStream stream)throws Exception{
		List<Map<String,Object>> sheetList = new ArrayList<Map<String,Object>>();
		
		List keyList = new ArrayList();
		
		 POIFSFileSystem fs = new POIFSFileSystem(stream);
         // 创建工作簿
         HSSFWorkbook workBook = new HSSFWorkbook(fs);
         
         log.info("工作表个数 :"+workBook.getNumberOfSheets());
        
         for(int i=0;i<1;i++){
        	 log.info("工作表名称"+workBook.getSheetName(i));
        	 
        	// 创建工作表
             HSSFSheet sheet = workBook.getSheetAt(i);
             int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
             
             if(rows>1){	//如果行数大于0
            	 sheet.getMargin(HSSFSheet.TopMargin);
            	 
            	 //循环行数
            	 for(int j=0;j<rows;j++){
            		 HSSFRow row = sheet.getRow(j);
            		 if(row!=null){
            			 //获得列的数据
            			 int cells = row.getLastCellNum();//获得列数
            			 
            			Map rowMap = new LinkedHashMap(); 
            			 
            			//初始化　row Map
            			if(keyList.size()>0){
            				for(Object o:keyList){
            					rowMap.put(o.toString()," ");
            				}
            			}
            			 
            			 
            			 //然后获得列的数据
            			 for(int k=0;k<cells;k++){
            				 HSSFCell cell = row.getCell(k);
            				 
            				 if (cell != null) {
	                            String value = "";
	                           	switch (cell.getCellType()) {
	                                 case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
	                                      if (HSSFDateUtil.isCellDateFormatted(
	                                      cell)) {
	                                      //如果是date类型则 ，获取该cell的date值
	                                      value = HSSFDateUtil.getJavaDate(
	                                      cell.getNumericCellValue()).
	                                      toString();
	                                      }else{//纯数字
	                                      
	                                     value = String.valueOf(cell
	                                             .getNumericCellValue());
	                                      }
	                                     break;
	                                 /* 此行表示单元格的内容为string类型 */
	                                 case HSSFCell.CELL_TYPE_STRING: // 字符串型
	                                     value = cell.getRichStringCellValue()
	                                             .toString();
	                                     break;
	                                 case HSSFCell.CELL_TYPE_FORMULA://公式型
	                                     //读公式计算值
	                                      value = String.valueOf(cell.getNumericCellValue());
	                                      if(value.equals("NaN")){//如果获取的数据值为非法值,则转换为获取字符串
	                                          
	                                          value = cell.getRichStringCellValue().toString();
	                                      }
	                                 break;
	                                 case HSSFCell.CELL_TYPE_BOOLEAN://布尔
	                                      value = " "
	                                      + cell.getBooleanCellValue();
	                                  break;
	                                 /* 此行表示该单元格值为空 */
	                                 case HSSFCell.CELL_TYPE_BLANK: // 空值
	                                     value = "";
	                                     break;
	                                 case HSSFCell.CELL_TYPE_ERROR: // 故障
	                                     value = "";
	                                     break;
	                                 default:
	                                     value = cell.getRichStringCellValue().toString();
                                 }
	                           	
	                           	//把第一行放进 List key 里
	            				 if(j==0){
	            					 keyList.add(value);
	            					 continue;
	            				 }
	                           	
	            				 if(k<keyList.size()){
	            					 rowMap.put(keyList.get(k), value);
	            				 }else{
	            					 log.info("此列没有标题");
	            				 }

                             } 

            			 }
            			 
            			 //第一行不添加
            			 if(j>0){
            				 sheetList.add(rowMap);
            			 }
            		 }
            	 }
             }
         }
         return sheetList;
	}
	
	 /** 
     * @Title: exportExcel 
     * @Description: 导出Excel的方法
     * @author: EX-WANGJIANQIANG001 @ 2012-8-31 下午03:49:08
     * @param workbook 
     * @param sheetNum 
     * @param sheetTitle
     * @param headers
     * @param result
     * @param out
     * @throws Exception    
     */    
    public void exportExcel(Workbook workbook,int sheetNum,String sheetTitle, String[] headers, List<List<String>> result, OutputStream out) throws Exception{  
        // 生成一个表格  
		Sheet sheet = workbook.createSheet();
		workbook.setSheetName(sheetNum,sheetTitle);
			// 生成一个样式  
		CellStyle style = workbook.createCellStyle();  
        // 设置样式  
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        // 生成一个字体  
        Font font = workbook.createFont();  
        font.setColor(HSSFColor.BLACK.index);  
        font.setFontHeightInPoints((short) 12);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        // 把字体应用到当前的样式  
        style.setFont(font);  
          
        // 指定当单元格内容显示不下时自动换行  
        style.setWrapText(true);  
          
        // 产生表格标题行  
        Row row = sheet.createRow(0);  
        for (int i = 0; i < headers.length; i++) {  
            Cell cell = row.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text.toString());
        }  
        // 遍历集合数据，产生数据行  
        if(result != null){  
            int index = 1;  
            for(List<String> m:result){  
                row = sheet.createRow(index);  
                int cellIndex = 0;  
               for(Object str:m){
                Cell cell = row.createCell(cellIndex);  
                cell.setCellValue(String.valueOf(str));  
                cellIndex++;  
              }
              index++;  
            }     
        }  
    }
}
